package cn.melonlib.test;

import cn.com.qtgl.apis.iam.comp.service.UserService;
import cn.melonlib.MainBootApplication;
import cn.melonlib.code.generator.comp.service.WorkerServiceDescriptionService;
import cn.melonlib.cores.jpa.comp.Dao;
import cn.melonlib.cores.jpa.model.bean.collections.JsonMap;
import cn.melonlib.tools.db.schemas.comp.dao.ModelDescriptionRetrieval;
import cn.melonlib.tools.db.schemas.comp.dao.impl.MysqlDatabaseSchemasDao;
import cn.melonlib.tools.db.schemas.model.views.ModelDescription;
import com.fasterxml.jackson.databind.ObjectMapper;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.export.ooxml.JRDocxExporter;
import net.sf.jasperreports.export.SimpleExporterInput;
import net.sf.jasperreports.export.SimpleOutputStreamExporterOutput;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.intellij.lang.annotations.Language;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.test.context.ActiveProfiles;

import javax.annotation.Resource;
import javax.persistence.Query;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@SpringBootTest(classes = MainBootApplication.class)
@ActiveProfiles("test")
public class Etest {

    @Resource
    private ModelDescriptionRetrieval modelDescriptionRetrieval;

    @Resource
    private WorkerServiceDescriptionService workerServiceDescriptionService;

    @Resource
    private Dao dao;

    @Resource
    private ObjectMapper jsonMapper;

    @Test
    public void createSchemaTest()throws Exception{
//        List<DatabaseTable> tables=databaseSchemasDao.getTables("teach");
//         FileWriter.create(new File("db_schema.json"), Charset.defaultCharset())
//                 .write(jsonMapper.writeValueAsString(tables));
        System.out.println(MysqlDatabaseSchemasDao.getTables());
    }

    @Test
    public void reportTest() throws Exception{
                List<ModelDescription> teachTables=modelDescriptionRetrieval.getModels("teach",JsonMap.createMap());
                List<ModelDescription> newsTables=modelDescriptionRetrieval.getModels("news",JsonMap.createMap());
                List<ModelDescription> leanringTables=modelDescriptionRetrieval.getModels("learning",JsonMap.createMap());
//         FileWriter.create(new File("db_schema.json"), Charset.defaultCharset())
//                 .write(jsonMapper.writeValueAsString(tables));
        ClassPathResource resource=new ClassPathResource("/database_schemas.jrxml");
        ClassPathResource subresource=new ClassPathResource("/database_tables.jrxml");
//        ClassPathResource imgRes=new ClassPathResource("/reports/logo.png");
        try(InputStream in=resource.getInputStream();InputStream subIn=subresource.getInputStream()){
            JasperReport jasperReport= JasperCompileManager.compileReport(in);
            JasperReport jasperReportSub= JasperCompileManager.compileReport(subIn);
            JasperPrint teachPrint= JasperFillManager.fillReport(jasperReport, JsonMap.createMap()
                            .append("tables",teachTables)
                            .append("columnsPath",jasperReportSub)
                            .append("title","教务系统数据库表结构")
                            .append("showTables",List.of(JsonMap.createMap().append("data",teachTables)))
                    ,new JREmptyDataSource());
            JasperPrint newsPrint= JasperFillManager.fillReport(jasperReport, JsonMap.createMap()
                            .append("tables",newsTables)
                            .append("columnsPath",jasperReportSub)
                            .append("title","新闻公告数据库表结构")
                            .append("showTables",List.of(JsonMap.createMap().append("data",newsTables)))
                    ,new JREmptyDataSource());
            JasperPrint learningPrint= JasperFillManager.fillReport(jasperReport, JsonMap.createMap()
                            .append("tables",newsTables)
                            .append("columnsPath",jasperReportSub)
                            .append("title","教学系统数据库表结构")
                            .append("showTables",List.of(JsonMap.createMap().append("data",newsTables)))
                    ,new JREmptyDataSource());
            JRDocxExporter exporter=new JRDocxExporter();
            exporter.setExporterInput(new SimpleExporterInput(teachPrint));
            exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("/service/reports/教务系统数据结构.docx")));
            exporter.exportReport();
            exporter.setExporterInput(new SimpleExporterInput(newsPrint));
            exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("/service/reports/新闻公告数据结构.docx")));
            exporter.exportReport();
            exporter.setExporterInput(new SimpleExporterInput(learningPrint));
            exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("/service/reports/教学系统数据结构.docx")));
            exporter.exportReport();
            byte [] data= JasperExportManager.exportReportToPdf(teachPrint);
            JasperExportManager.exportReportToHtmlFile(teachPrint,"/service/reports/教务系统数据结构.html");
            FileUtils.writeByteArrayToFile(new File("/service/reports/教务系统数据结构.pdf"),data);
            byte [] newsdata= JasperExportManager.exportReportToPdf(newsPrint);
            JasperExportManager.exportReportToHtmlFile(newsPrint,"/service/reports/新闻公告数据结构.html");
            FileUtils.writeByteArrayToFile(new File("/service/reports/新闻公告数据结构.pdf"),newsdata);
            byte [] learningdata= JasperExportManager.exportReportToPdf(learningPrint);
            JasperExportManager.exportReportToHtmlFile(learningPrint,"/service/reports/教学系统数据结构.html");
            FileUtils.writeByteArrayToFile(new File("/service/reports/教学系统数据结构.pdf"),learningdata);
        }
    }

    @Test
    public void parseSqlTest() throws Exception{
        String createSql= """
                CREATE TABLE `cms_article` (
                  `id` varchar(100) NOT NULL,
                  `create_date_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
                  `creator` longtext DEFAULT NULL COMMENT '创建人快照',
                  `creator_id` varchar(255) DEFAULT NULL COMMENT '创建人ID',
                  `last_modified_date_time` bigint(20) DEFAULT NULL COMMENT '最后修改时间',
                  `sort_num` bigint(20) DEFAULT NULL COMMENT '默认排序字段',
                  `tenant` longtext DEFAULT NULL COMMENT '租户快照信息',
                  `tenant_id` varchar(255) DEFAULT NULL COMMENT '租户ID',
                  `approved_date_time` bigint(20) DEFAULT NULL COMMENT '审核时间',
                  `approved_notice` longtext DEFAULT NULL COMMENT '审核批复内容',
                  `approved_status` varchar(100) DEFAULT NULL COMMENT '审核状态',
                  `approver` longtext DEFAULT NULL COMMENT '审核人',
                  `approver_id` varchar(255) DEFAULT NULL COMMENT '审核人Id',
                  `article_type` varchar(100) DEFAULT NULL COMMENT '文章类型',
                  `channels` varchar(1000) DEFAULT NULL COMMENT '提醒渠道',
                  `cms_permission_type` varchar(100) DEFAULT NULL COMMENT '权限类型',
                  `content` longtext DEFAULT NULL COMMENT '正文',
                  `department` varchar(255) DEFAULT NULL COMMENT '发布部门(作者)',
                  `display_end_date_time` bigint(20) DEFAULT NULL COMMENT '显示结束时间，为空或者0为不限制',
                  `display_start_date_time` bigint(20) DEFAULT NULL COMMENT '显示开始时间，为空或者0为不限制',
                  `end_date_time` bigint(20) DEFAULT NULL COMMENT '结束时间',
                  `feed_back_num` bigint(20) DEFAULT NULL COMMENT '反馈数量',
                  `feedback` bit(1) DEFAULT NULL COMMENT '是否需要反馈',
                  `important` int(11) DEFAULT NULL COMMENT '重要，标星,0 不重要 1~5 表示几颗星',
                  `master` varchar(255) DEFAULT NULL COMMENT '主持人',
                  `out_link` bit(1) DEFAULT NULL COMMENT '是否外链',
                  `participants` varchar(255) DEFAULT NULL COMMENT '参与人',
                  `path` varchar(2000) DEFAULT NULL COMMENT '栏目路径',
                  `publish_date_time` bigint(20) DEFAULT NULL COMMENT '第一次发布时间',
                  `published` bit(1) DEFAULT NULL COMMENT '是否发布',
                  `publisher` longtext DEFAULT NULL COMMENT '发布人',
                  `publisher_id` varchar(255) DEFAULT NULL COMMENT '发布人',
                  `push_flag` bit(1) DEFAULT NULL COMMENT '是否推送',
                  `re_publish_date_time` bigint(20) DEFAULT NULL COMMENT '再次发布时间',
                  `re_publisher` longtext DEFAULT NULL COMMENT '再次发布人',
                  `re_publisher_id` varchar(255) DEFAULT NULL COMMENT '再次发布人',
                  `remind` bit(1) DEFAULT NULL COMMENT '是否已经提醒',
                  `remind_date_time` bigint(20) DEFAULT NULL COMMENT '触发提醒时间',
                  `start_date_time` bigint(20) DEFAULT NULL COMMENT '开始时间',
                  `status` bit(1) DEFAULT NULL COMMENT '是否启用',
                  `sub_title` varchar(2000) DEFAULT NULL COMMENT '副标题',
                  `summary` longtext DEFAULT NULL COMMENT '摘要',
                  `sync` bit(1) DEFAULT NULL COMMENT '是否为推送数据',
                  `sync_id` varchar(255) DEFAULT NULL COMMENT '推送信息ID',
                  `title` varchar(1000) DEFAULT NULL COMMENT '标题',
                  `title_img` varchar(1000) DEFAULT NULL COMMENT '标题图片',
                  `top` bit(1) DEFAULT NULL COMMENT '置顶',
                  `classify_id` varchar(100) DEFAULT NULL COMMENT '新闻类别',
                  `channel_id` varchar(100) DEFAULT NULL COMMENT '所属栏目',
                  PRIMARY KEY (`id`),
                  KEY `FKhtqo6yi6xqhyrcehitvebd30d` (`classify_id`),
                  KEY `FKn6yfihv5wki6o6xniv4dh0sv4` (`channel_id`),
                  CONSTRAINT `FKhtqo6yi6xqhyrcehitvebd30d` FOREIGN KEY (`classify_id`) REFERENCES `cms_article_classify` (`id`),
                  CONSTRAINT `FKn6yfihv5wki6o6xniv4dh0sv4` FOREIGN KEY (`channel_id`) REFERENCES `cms_channel` (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章'
                """;

        CreateTable statement=(CreateTable)CCJSqlParserUtil.parse(createSql);
        List<ColumnDefinition> columnDefinitions=statement.getColumnDefinitions();
        Table table=statement.getTable();
        System.out.println(statement.getTable().getName());
        statement.getTableOptionsStrings().forEach(System.out::println);
        List<String> options=statement.getTableOptionsStrings();
        System.out.println(options.get(options.indexOf("COMMENT") + 2).replaceAll("\\\"","").replaceAll("'",""));
        columnDefinitions.forEach(c->{
            String typeName=c.getColDataType().getDataType();
            if(Objects.nonNull(c.getColDataType().getArgumentsStringList())&&c.getColDataType().getArgumentsStringList().size()>0){
                typeName+="("+c.getColDataType().getArgumentsStringList().stream().collect(Collectors.joining(","))+")";
            }
            System.out.println(c.getColumnName().replaceAll("`","")+":"+typeName);
            System.out.println(c.getColumnSpecs());

        });
    }

    @Test
    public void createVueModule()throws Exception{
        System.out.println(workerServiceDescriptionService.importTypes(UserService.class));
//        schemaTools.createVueModule("D:\\home\\hector\\projects\\ktgl\\teach\\frontend\\src\\pages", ExamLessonService.class,SchemaTools.VUE_SERVICE);
//        schemaTools.createVueModule("D:\\home\\hector\\projects\\ktgl\\teach\\frontend\\src\\pages", InvigilatorService.class,SchemaTools.VUE_ALL);
    }

    @Test
    public void sqlCreator() throws Exception{
        Pattern p=Pattern.compile("^(\\d{4}-\\d{2}-\\d{2}).*$");
        System.out.println("aaaaa");
        List<Object[]> data=dao.query(em->{
            @Language("SQL") String query="select id,title from cms_article";
           Query q=em.createNativeQuery(query);
           return q.getResultList();
        }).getResult();

        try(PrintWriter writer=new PrintWriter(new OutputStreamWriter(new FileOutputStream("/home/hector/document/update_article.sql"), StandardCharsets.UTF_8))){
            data.forEach(d->{
                String title=d[1].toString().trim();
                String id=d[0].toString();
                Matcher m= p.matcher(title);
                if(m.matches()){
                    m.group(1);
                    try {
                        long time= DateUtils.parseDate(m.group(1),"yyyy-MM-dd").getTime();
                        @Language("SQL") String sql="update cms_article set publish_date_time="+time+",re_publish_date_time="+time+" where id='"+id+"';";
                        writer.println(sql);
                    } catch (ParseException e) {
                        throw new RuntimeException(e);
                    }

                }
            });
        }


    }
}
